/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50141
Source Host           : localhost:3306
Source Database       : sport

Target Server Type    : MYSQL
Target Server Version : 50141
File Encoding         : 65001

Date: 2014-02-19 08:26:16
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `clubs_img`
-- ----------------------------
DROP TABLE IF EXISTS `clubs_img`;
CREATE TABLE `clubs_img` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`clubid`  int(11) NOT NULL ,
`srcid`  int(11) NOT NULL ,
`type`  smallint(6) NOT NULL DEFAULT 0 ,
`name`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`tag`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`short`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`description`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`path`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`image`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`format`  char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`width`  double NOT NULL ,
`height`  double NOT NULL ,
`status`  tinyint(4) NOT NULL ,
`created`  datetime NOT NULL ,
`modified`  datetime NOT NULL ,
`logid`  int(11) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
INDEX `IX_club_src_type` USING BTREE (`clubid`, `srcid`, `type`) ,
INDEX `IX_created` USING BTREE (`created`) ,
INDEX `IX_status` USING BTREE (`status`) 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of clubs_img
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Procedure structure for `clubs_delete`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_delete`;
CREATE PROCEDURE `clubs_delete`(ploginid int(11),pclubid int(11))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rLogo VARCHAR(150) DEFAULT NULL;
	DECLARE rType TINYINT(4) DEFAULT 0;
	DECLARE rPerm TINYINT(4) DEFAULT 0;
	SET rType = _admin_role(ploginid);
	SET rPerm = _admin_check_permission(1<<3,ploginid,'club');
	IF rErrCode=0 AND (rPerm=0 OR rType < 5) THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		SELECT logo INTO rLogo FROM clubs WHERE clubid=pclubid;
		DELETE FROM clubs WHERE clubid=pclubid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `clubs_img_add`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_img_add`;
CREATE PROCEDURE `clubs_img_add`(ploginid int,pclubid int,psrcid int,ptype tinyint(4),pname varchar(100),ptag varchar(150),pshort varchar(255),pdescription varchar(255),ppath varchar(150),pimage varchar(150),pformat char(4),pwidth double,pheight double)
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rId INT DEFAULT 0;
	IF rErrCode=0 AND 
			_admin_check_permission(1<<1,ploginid,'club')=0 AND 
			_admin_check_permission(1<<1,ploginid,'club_img')=0 AND 
			ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN				
		INSERT clubs_img(clubid, srcid, type, name, tag,
								short, description, path, image, format, 
								width, height, status, created, modified,
								logid)
		VALUE(pname, rNameSe, pnickname, pcountry, plogo,
					pslug, pshort, pdescription, ptag, NOW(),
					NOW(), 1, ploginid);
		SET rId = LAST_INSERT_ID();	
	END IF;
	SELECT rErrCode AS errCode, rId AS id;
END;

-- ----------------------------
-- Procedure structure for `clubs_img_delete`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_img_delete`;
CREATE PROCEDURE `clubs_img_delete`(ploginid int(11),pid int(11))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rPath VARCHAR(150) DEFAULT NULL;
	DECLARE rImage VARCHAR(150) DEFAULT NULL;
	DECLARE rFormat CHAR(4) DEFAULT NULL;
	DECLARE rType TINYINT(4) DEFAULT 0;
	DECLARE rPerm TINYINT(4) DEFAULT 0;
	SET rType = _admin_role(ploginid);
	SET rPerm = _admin_check_permission(1<<3,ploginid,'club_img');
	IF rErrCode=0 AND (rPerm=0 OR rType < 5) THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		SELECT path,image,format INTO rPath, rImage, rFormat FROM clubs_img WHERE id=pid;
		DELETE FROM clubs_img WHERE clubid=pclubid;
	END IF;
	SELECT rErrCode AS errCode, rPath AS path, rImage AS image, rFormat AS format;
END;

-- ----------------------------
-- Procedure structure for `clubs_img_get`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_img_get`;
CREATE PROCEDURE `clubs_img_get`(pid int(11))
    READS SQL DATA
BEGIN	
		SELECT *
		FROM clubs_img
		WHERE id=pid;
END;

-- ----------------------------
-- Procedure structure for `clubs_img_getall_a`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_img_getall_a`;
CREATE PROCEDURE `clubs_img_getall_a`(ploginid int,pstatus tinyint,pclubid int,pstart date,pend date,poffset int,plimit smallint(6))
    READS SQL DATA
BEGIN
	#call: 1,-1,1,null,null,0,10
	DECLARE rFoundRows INT DEFAULT 0;
	DECLARE rType TINYINT DEFAULT 0;
	DECLARE rStatus TINYINT DEFAULT 0;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmpclubs_img(id bigint) ;	
	# permission view
	SET rType = _admin_role(ploginid);
	IF rType < 3 THEN SET rStatus = 3;
	ELSE SET rStatus = rType + 1;	
	END IF;
	SET @sqlStm = CONCAT('
												INSERT tmpclubs_img(id)
												SELECT SQL_CALC_FOUND_ROWS
														clubid
												FROM clubs_img
												WHERE id IS NOT NULL AND clubid=',pclubid,														
													IF(pstatus > -1, 
														CONCAT(' AND ABS(status)=',pstatus),
														CONCAT(' AND ABS(status)<',rStatus)
													),													
													IF(pstart <> '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(created,10) BETWEEN ''',pstart,''' AND ''',pend,''''),''),
													IF(pstart <> '0000-00-00' AND pend = '0000-00-00', CONCAT(' AND LEFT(created,10)=''',pstart,''''),''),
													IF(pstart = '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(created,10)=''',pend,''''),''),
												' ORDER BY clubid DESC
												LIMIT ',poffset,',',plimit
											);

	PREPARE stmt FROM @sqlStm;
	EXECUTE stmt;

	SET rFoundRows = FOUND_ROWS();
	
	SELECT SQL_SMALL_RESULT rFoundRows AS foundRows, B.*
	FROM tmpclubs_img A JOIN clubs_img B ON A.id=B.id;

	DROP TEMPORARY TABLE IF EXISTS tmpclubs_img;	
END;

-- ----------------------------
-- Procedure structure for `clubs_img_update`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_img_update`;
CREATE PROCEDURE `clubs_img_update`(ploginid int,pid int,pclubid int,psrcid int,ptype tinyint(4),pname varchar(100),ptag varchar(150),pshort varchar(255),pdescription varchar(255),ppath varchar(150),pimage varchar(150),pformat char(4),pwidth double,pheight double)
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rId INT DEFAULT 0;	
	DECLARE rPath VARCHAR(150) DEFAULT NULL;
	DECLARE rImage VARCHAR(150) DEFAULT NULL;
	DECLARE rFormat CHAR(4) DEFAULT NULL;
	IF rErrCode=0 AND 
			_admin_check_permission(1<<2,ploginid,'club')=0 AND 
			_admin_check_permission(1<<2,ploginid,'club_img')=0 AND 
			ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN	
		SELECT path,image,format INTO rPath, rImage, rFormat FROM clubs_img WHERE id=pid;
		UPDATE clubs_img 
		SET name=pname,
				tag=ptag,
				short=pshort,
				description=pdescription,
				path=ppath,
				image=pimage,
				format=pformat,
				width=pwidth,
				height=pheight,
				modified=NOW(),
				logid=plogid
		WHERE id=pid AND clubid=pclubid AND srcid=psrcid AND type=ptype;
	END IF;
	SELECT rErrCode AS errCode, rPath AS path, rImage AS image, rFormat AS format;
END;

-- ----------------------------
-- Procedure structure for `clubs_img_update_status`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_img_update_status`;
CREATE PROCEDURE `clubs_img_update_status`(ploginid int(11),pclubid int(11),pstatus tinyint(4),pperm tinyint(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND 
			_admin_check_permission(pperm,ploginid,'club')=0 AND 
			_admin_check_permission(pperm,ploginid,'club_img')=0 AND 
			ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode=0 THEN		
		UPDATE clubs_img SET `status`=pstatus WHERE id=pid;	
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Auto increment value for `clubs_img`
-- ----------------------------
ALTER TABLE `clubs_img` AUTO_INCREMENT=1;
